package com.bbms.dao.impl;

import java.util.HashMap;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.bbms.dao.ReaderDao;
import com.bbms.entity.Page;
import com.bbms.entity.Reader;
import com.bbms.util.HibernateUtil;
import com.bbms.util.StringUtil;

@Repository("ReaderDaoImpl")
public class ReaderDaoImpl implements ReaderDao{

	/*
	 * 添加读者信息
	 * @see com.bbms.dao.BaseDAO#save(java.lang.Object)
	 */
	@Override
	public void save(Reader t) {
		HibernateUtil.save(t);		
	}

	/*
	 * 更新读者信息
	 * @see com.bbms.dao.BaseDAO#update(java.lang.Object)
	 */
	@Override
	public void update(Reader t) {
		
		StringBuffer sql=new StringBuffer();
		sql.append(   "UPDATE bbms_user_reader R "
					+ "SET R.borrow_books_num="+ t.getBorrowBooksNum() +" ,"
					+ "R.borrow_num="+ t.getBorrowNum() +" ,R.balance="+ t.getBalance() +" ,R.category_id="+ t.getCategoryId() +" "
					+ "WHERE R.user_info_id="+ t.getUserInfoId() +" ");
		
		System.out.println("更改Reader的sql:  "+sql.toString());
		
		HibernateUtil.executeSqlUpdate(sql.toString());
	}

	/*
	 * 删除单个读者
	 * @see com.bbms.dao.BaseDAO#delete(java.lang.Object)
	 */
	@Override
	public void delete(Reader t) {
		HibernateUtil.delete(t);
	}

	/*
	 * 获取分页数据(无查询)
	 * @see com.bbms.dao.BaseDAO#getPage(int, int)
	 */
	@Override
	public Page getPage(int currentPage, int pageSize) {
		return null;
	}

	/*
	 * 获取全部信息（无分页）
	 * @see com.bbms.dao.BaseDAO#query()
	 */
	@Override
	public List<?> query() {
		return null;
	}

	/*
	 * 获取分页信息（查询）
	 * @see com.bbms.dao.ReaderDao#getPage(int, int, java.lang.String, java.lang.String, java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String)
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public Page getPage(int currentPage, int pageSize, String s_name, String s_account, String s_phone,
			Integer s_categoryId, String IDCard, String start_time, String end_time,  Integer endTime) {
		
			StringBuffer sql=new StringBuffer();
			StringBuffer count=new StringBuffer();
		
			if (endTime!=null&&endTime==1) {
				
				sql.append(	  "SELECT reader.id AS id, reader.user_info_id AS userInfoId, U.`name` AS `name`, "
							+ "U.account AS account, U.IDCard AS IDCard, reader.borrow_books_num AS borrowBooksNum, "
							+ "reader.borrow_num AS borrowNum, reader.category_id AS categoryId, category.`name` AS category, "
							+ "reader.balance AS balance, U.phone AS phone, U.birthday AS birthday "
							+ "FROM bbms_borrow_record record "
							+ "LEFT JOIN bbms_user_reader reader "
							+ "ON reader.id=record.user_reader_id "
							+ "LEFT JOIN bbms_user_info U "
							+ "ON reader.user_info_id=U.id "
							+ "LEFT JOIN bbms_user_reader_category category "
							+ "ON category.id=reader.category_id "
							+ "WHERE DATE(record.restore_date) = DATE(NOW())");
				
				count.append( "SELECT COUNT(reader.id) AS count "
							+ "FROM bbms_borrow_record record "
							+ "LEFT JOIN bbms_user_reader reader "
							+ "ON reader.id=record.user_reader_id "
							+ "LEFT JOIN bbms_user_info U "
							+ "ON reader.user_info_id=U.id "
							+ "LEFT JOIN bbms_user_reader_category category "
							+ "ON category.id=reader.category_id "
							+ "WHERE DATE(record.restore_date) = DATE(NOW())");
			
				sql.append(" LIMIT " + ((currentPage - 1)* pageSize) + "," + pageSize +" ");
				
			}else {
				
				sql.append(   "SELECT  R.id AS id, R.user_info_id AS userInfoId, R.category_id AS categoryId, C.`name` AS category, "
							+ "R.borrow_books_num AS borrowBooksNum, R.borrow_num AS borrowNum, R.balance AS balance, "
							+ "U.`name` AS `name`, U.account AS account, U.IDCard AS IDCard, U.phone AS phone, U.birthday AS birthday "
							+ "FROM bbms_user_reader AS R "
							+ "LEFT JOIN bbms_user_info AS U "
							+ "ON R.user_info_id=U.id "
							+ "LEFT JOIN bbms_user_reader_category AS C "
							+ "ON R.category_id=C.id "
							+ "WHERE 1=1 ");
				
				count.append( "SELECT COUNT(*) AS count "
							+ "FROM bbms_user_reader AS R "
							+ "LEFT JOIN bbms_user_info AS U "
							+ "ON R.user_info_id=U.id "
							+ "LEFT JOIN bbms_user_reader_category AS C " 
							+ "ON C.id=R.category_id "
							+ "WHERE 1=1 ");
				
				if (!StringUtil.isEmpty(s_name)) {
					sql.append(" AND U.name LIKE '%"+ s_name +"%'");
					count.append(" AND U.name LIKE '%"+ s_name +"%'");
				}
				if (!StringUtil.isEmpty(s_account)) {
					sql.append(" AND U.account LIKE '%"+ s_account +"%'");
					count.append(" AND U.account LIKE '%"+ s_account +"%'");
				}
		        if (!StringUtil.isEmpty(s_phone)) {
		        	sql.append(" AND U.phone LIKE '%"+ s_phone +"%'");
		        	count.append(" AND U.phone LIKE '%"+ s_phone +"%'");
				}
		        if (s_categoryId!=null) {
		        	sql.append(" AND R.category_id ="+ s_categoryId +"");
		        	count.append(" AND R.category_id ="+ s_categoryId +"");
				}
		        if (!StringUtil.isEmpty(IDCard)) {
		        	sql.append(" AND U.IDCard LIKE '%"+ IDCard +"%'");
		        	count.append(" AND U.IDCard LIKE '%"+ IDCard +"%'");
				}
		        if (!StringUtil.isEmpty(start_time)) {
		        	sql.append(" AND UNIX_TIMESTAMP(U.birthday) >=UNIX_TIMESTAMP('" + start_time + "')");
		        	count.append(" AND UNIX_TIMESTAMP(U.birthday) >=UNIX_TIMESTAMP('" + start_time + "')");
				}
		        if (!StringUtil.isEmpty(end_time)) {
		        	sql.append(" AND UNIX_TIMESTAMP(U.birthday) <=UNIX_TIMESTAMP('" + end_time + "')");
		        	count.append(" AND UNIX_TIMESTAMP(U.birthday) <=UNIX_TIMESTAMP('" + end_time + "')");
		        }
				
		        sql.append(" LIMIT " + ((currentPage - 1)* pageSize) + "," + pageSize +" ");
			}
			
	        System.out.println(sql.toString());
	        
			Page page=new Page();
			List<?> list=HibernateUtil.executeSqlQuery(sql.toString());
			page.setPageNo(currentPage);
			page.setPageSize(pageSize);
			page.setRows(list);
			
			List<HashMap> l=(List<HashMap>)HibernateUtil.executeSqlQuery(count.toString());
			page.setTotal(Integer.parseInt(l.get(0).get("count").toString()));
			
			return page;
		
	}

	/*
	 *根绝账号和密码查找管理员
	 * @see com.bbms.dao.ReaderDao#findCurrentManager(java.lang.String, java.lang.String)
	 */
	@Override
	public List<?> findCurrentManager(String account, String password) {
	
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT manager.id , U.`name` "
					+"FROM bbms_user_manager manager "
					+"LEFT JOIN bbms_user_info U "
					+"ON U.id=manager.user_info_id WHERE 1=1 "
					+"AND U.account LIKE '"+ account +"' " 
					+"AND U.`password` LIKE '"+ password +"'");
		
		List<?> list=HibernateUtil.executeSqlQuery(sql.toString());
		
		return list;
	}

	/*
	 * 根据读者ID查找对应用户
	 * @see com.bbms.dao.ReaderDao#findUserByReaderId(int)
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<HashMap> findUserByReaderId(int readerId) {
		
		StringBuffer sql=new StringBuffer();
		
		sql.append("SELECT reader.id AS id, reader.user_info_id AS userId "
					+"FROM bbms_user_reader reader "
					+"WHERE reader.id="+ readerId +" ");
							
		List<HashMap> list=(List<HashMap>)HibernateUtil.executeSqlQuery(sql.toString());
		
		return list;
	}

}
